###############################################################################
# CA cleaning
#
# 11 August 2022 && 22 January 2024
# sbaltz at mit
###############################################################################

import pandas as pd

RAW_FILE_NAME = "modoc.csv"
SAVE_FILE_NAME = "modoc_cleaned.csv"
COUNTY_NAME = "MODOC"

ct = pd.read_csv(RAW_FILE_NAME)

cleaned = pd.DataFrame()

for i in range(len(ct)):
    if "CONTEST: " in str(ct["office"][i]):
        office = str(ct["office"][i].replace("CONTEST: ",""))
    if "PRECINCT" in str(ct["office"][i]):
        precinct = str(ct["office"][i].replace("PRECINCT ",""))
    if str(ct["original"][i]) != "nan" and str(ct["audited"][i]) != "nan":
        try:
            state = "CALIFORNIA"
            county = COUNTY_NAME
            precinct = precinct
            candidate = str(ct["candidate"][i])
            original = int(ct["original"][i])
            audited = int(ct["audited"][i])
            difference = audited - original
            method = "MANUAL"
            if "Ballots Cast" not in candidate:
                cleaned = cleaned.append(pd.DataFrame([[state,county,precinct,
                            office,\
                            candidate,original,audited,difference,method]]),\
                            ignore_index = True)
        except Exception as inst:
            print(f"ROW {i} NOT SAVED: ", inst)

cleaned.columns = ["state","county","precinct","office","candidate","original",\
                   "audited",\
                   "difference","type"]

cleaned.to_csv(SAVE_FILE_NAME, index=False)
